import pymysql
from config.db_config import DB_CONFIG
import logging


def get_foreign_keys():
    """
    获取数据库中所有表的外键关系
    :return: 包含外键关系的列表，每个元素是一个包含表名、约束名、列名、引用表和引用列的元组
    """
    query = """
        SELECT 
            TABLE_NAME, 
            CONSTRAINT_NAME, 
            COLUMN_NAME, 
            REFERENCED_TABLE_NAME, 
            REFERENCED_COLUMN_NAME
        FROM information_schema.KEY_COLUMN_USAGE
        WHERE REFERENCED_TABLE_NAME IS NOT NULL
          AND TABLE_SCHEMA = DATABASE()
    """
    
    def _get_foreign_keys(cursor):
        cursor.execute(query)
        return cursor.fetchall()
    
    return db_operation(_get_foreign_keys)


def deleteAllData():
    """
    删除数据库中所有表的数据，考虑外键依赖关系
    :param db_config: 数据库配置对象
    """
    # 获取所有外键关系
    foreign_keys = get_foreign_keys()
    
    # 删除所有外键约束
    def _drop_foreign_keys(cursor):
        for fk in foreign_keys:
            cursor.execute(f'ALTER TABLE {fk.table_name} DROP FOREIGN KEY {fk.constraint_name}')
    db_operation(_drop_foreign_keys)
    
    # 删除所有表的数据
    def _delete_table_data(cursor):
        tables = get_tables()
        for table in tables:
            cursor.execute(f'DELETE FROM {table}')
    db_operation(_delete_table_data)
    
    # 重新建立外键约束
    def _add_foreign_keys(cursor):
        for fk in foreign_keys:
            cursor.execute(f'ALTER TABLE {fk.table_name} ADD CONSTRAINT {fk.constraint_name} FOREIGN KEY ({fk.column_name}) REFERENCES {fk.referenced_table}({fk.referenced_column})')
    db_operation(_add_foreign_keys)

    return True

def get_tables():
    """
    获取数据库中的所有表名
    :return: 包含所有表名的列表
    """
    query = "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE()"

    def _get_tables(cursor):
        cursor.execute(query)
        return [row['TABLE_NAME'] for row in cursor.fetchall()]

    return db_operation(_get_tables)


def dropAllTables():
    """
    删除数据库中的所有表，处理外键约束
    :return: 操作结果
    """
    # 获取所有外键关系
    foreign_keys = get_foreign_keys()
    
    # 删除所有外键约束
    def _drop_foreign_keys(cursor):
        for fk in foreign_keys:
            cursor.execute(f'ALTER TABLE {fk[0]} DROP FOREIGN KEY {fk[1]}')
    db_operation(_drop_foreign_keys)
    
    # 删除所有表
    def _drop_tables(cursor):
        tables = get_tables()
        for table in tables:
            cursor.execute(f'DROP TABLE {table}')
    db_operation(_drop_tables)
    
    return True


def execute_single_value_query(sql_query, **kwargs):
    def query_callback(cursor):
        cursor.execute(sql_query,kwargs)
        result = cursor.fetchone()
        if result:
            column_name = cursor.description[0][0]
            return  result[column_name]
        return None
    return db_operation(query_callback)
def execute_sql_file(file_path):
    """
    执行指定路径的SQL文件
    :param file_path: SQL文件路径
    """
    def _execute_sql_file(cursor):
        with open(file_path, 'r', encoding='utf-8') as f:
            sql_commands = f.read()
        for sql in sql_commands.split(';'):
            sql = sql.strip()
            if sql:
                cursor.execute(sql)
    return db_operation(_execute_sql_file)



# 配置SQL专用日志
# 修改日志配置部分
sql_logger = logging.getLogger('SQL_LOGGER')
# 移除硬编码的setLevel，改为由pytest命令行参数控制
handler = logging.StreamHandler()
handler.setFormatter(logging.Formatter('SQL>> %(message)s'))
sql_logger.addHandler(handler)
sql_logger.propagate = False  # 防止日志被父logger处理

def db_operation(callback):
    """
    封装数据库操作，处理连接和异常（带日志功能）
    :param callback: 数据库操作回调函数
    :param db_config: 数据库配置
    :return: 操作结果
    """
    class CursorProxy:
        def __init__(self, real_cursor):
            self._real_cursor = real_cursor
        
        def __getattr__(self, name):
            return getattr(self._real_cursor, name)
            
        def execute(self, query, args=None):
            if sql_logger.isEnabledFor(logging.DEBUG):  # 只有在DEBUG级别时才记录
                sql_logger.debug(f"执行SQL: {query}")
                if args:
                    sql_logger.debug(f"参数: {args}")
            result = self._real_cursor.execute(query, args)
            if sql_logger.isEnabledFor(logging.DEBUG):
                sql_logger.debug(f"影响行数: {self._real_cursor.rowcount}")
            return result
            
        def executemany(self, query, args_list):
            if sql_logger.isEnabledFor(logging.DEBUG):
                sql_logger.debug(f"批量执行SQL: {query}")
                sql_logger.debug(f"参数列表: {args_list}")
            return self._real_cursor.executemany(query, args_list)

    connection = pymysql.connect(**DB_CONFIG)
    cursor = connection.cursor()
    try:
        result = callback(CursorProxy(cursor))  # 传入代理对象
        connection.commit()
        return result
    except Exception as e:
        sql_logger.error(f"数据库操作错误: {e}")
        connection.rollback()
        raise
    finally:
        cursor.close()
        connection.close()


def insert_single_row_and_get_id(insert_sql, **kwargs):
    """
    执行INSERT语句并返回最后插入的ID
    
    参数:
        insert_sql: str - 要执行的INSERT SQL语句
        **kwargs: dict - SQL参数
        
    返回:
        int: 最后插入行的ID
    """
    def insert_callback(cursor):
        cursor.execute(insert_sql, kwargs)
        return cursor.lastrowid

    return db_operation(insert_callback)